import os
import re
import sys
import time
import datetime
import subprocess
import xlsxwriter
import statistics


class OG(object):
    dnpath = None
    procid = None
    port = None
    xlogpath = None
    
    @staticmethod
    def detect(dnpath):
        with open(os.path.join(dnpath, 'postmaster.pid'), 'r') as f:
            lines = f.readlines()
            OG.dnpath = dnpath
            OG.procid = lines[0].strip()
            OG.port = lines[3].strip()
            OG.xlogpath = os.path.join(dnpath, 'pg_xlog')
            print('OG info: dnpath={0}  procid={1}  port={2}'.format(OG.dnpath, OG.procid, OG.port))

    def __init__(self):
        assert False


class Exceler(object):
    workfile = None
    workbook = None
    
    @staticmethod
    def setExcel(file):
        Exceler.workfile = file
        Exceler.workbook = xlsxwriter.Workbook(file)
    
    @staticmethod
    def recordExcel(sheetname, data, xname='X', yname='Y'):
        def _column_name(n):
            name = ""
            while n > 0:
                n -= 1
                name = chr(ord('A') + n % 26) + name
                n //= 26
            return name
        
        sheet = Exceler.workbook.add_worksheet(sheetname)
        
        coltail = _column_name(len(data[0]))
        for row_num, row_data in enumerate(data):
            for col_num, cell_data in enumerate(row_data):
                sheet.write(row_num, col_num, cell_data)
        
        chart = Exceler.workbook.add_chart({'type': 'line'})
        for row_num in range(1, len(data) + 1):
            chart.add_series({
                'name': f'={sheetname}!$A${row_num + 1}',
                'categories': f'={sheetname}!$B$1:${coltail}$1',
                'values': f'={sheetname}!$B${row_num + 1}:${coltail}${row_num + 1}'
            })
        chart.set_x_axis({'name': xname})
        chart.set_y_axis({'name': yname})
        
        sheet.insert_chart('A1', chart)
    
    @staticmethod
    def dumpExcel():
        Exceler.workbook.close()
        Exceler.workfile = None
        Exceler.workbook = None
        
    def __init__(self):
        assert False


class Moniter(object):
    def __init__(self):
        assert Exceler.workfile is not None and Exceler.workbook is not None
        self.counter = 0
        self.timer = []
        
    def timer_counter(func):
        def wrapper(self, *args, **kwargs):
            current_time = datetime.datetime.now().time()
            self.timer.append(f"{current_time.hour:02d}:{current_time.minute:02d}:{current_time.second:02d}")
            
            result = func(self, *args, **kwargs)
            
            self.counter += 1
            return result
            
        return wrapper
        
    def add_title(self, data):
        title = ['time'] + self.timer
        assert len(title) == len(data[0])
        return [title] + data


class ThreadMoniter(Moniter):
    def __init__(self, procid):
        super(ThreadMoniter, self).__init__()
        self.procid = procid
        self.data = {}   # { thrd name: {tid : [cpu], ...}}
        self.counter = 0
        self.timer = []

    @timer_counter
    def record(self):
        
        cmd = 'top -Hp {} -b -n 1'.format(self.procid)
        p = subprocess.Popen(cmd, stdout=subprocess.PIPE, shell=True)
        output = p.stdout.read().decode("utf-8")

        data = []
        lines = output.split('\n\n')[1].split('\n')[1:]
        for line in lines:
            # 2092070 carrot    20   0 6321536 801664 374592 S   0.0   1.6   0:18.00 gaussdb
            part = line.split()
            if len(part) < 12:
                continue

            data.append((part[11], part[0], float(part[8])))

        for name, tid, cpu in data:
            if self.data.get(name) is None:
                self.data[name] = {}
            if self.data[name].get(tid) is None:
                self.data[name][tid] = [0] * self.counter

            self.data[name][tid].append(cpu)

        for name in self.data:
            for thid in self.data[name]:
                if len(self.data[name][thid]) == self.counter:
                    self.data[name][thid].append(0)

    def analyze_all(self):
        
        data = [['thread'] + self.timer]
        for name in self.data:
            for thid in self.data[name]:
                data.append(['{}-{}'.format(name, thid)] + self.data[name][thid])
        
        return 'all-thread', data

    def analyze_statistic(self):
        data = [['thread'] + self.timer]
        for name in self.data:
            group = []
            for thid in self.data[name]:
                group.append(['{}-{}'.format(name, thid)] + self.data[name][thid])
            
            transposed_scores = list(map(list, zip(*group)))
            averages = [statistics.mean(subject) for subject in transposed_scores[1:]]
            maximums = [max(subject) for subject in transposed_scores[1:]]
            minimums = [min(subject) for subject in transposed_scores[1:]]
            medians = [statistics.median(subject) for subject in transposed_scores[1:]]
            
            data.append([f'{name}-avg'] + averages)
            data.append([f'{name}-max'] + maximums)
            data.append([f'{name}-min'] + minimums)
            data.append([f'{name}-med'] + medians)
        
        return 'analyze-thread', data


class XlogNumMoniter(Moniter)
    def __init__(self, xlogpath):
        super(XlogNumMoniter, self).__init__()
        self.xlogpath = xlogpath
        self.data = ['xlog-num']
        
    @timer_counter
    def record(self):
        num = len(os.listdir(self.xlogpath))
        self.data.append(num)
    
    def analyze(self):
        return 'xlog-num', self.add_title(self.data)


class TableSizeMoniter(Moniter)
    def __init__(self, port, dbname):
        super(TableSizeMoniter, self).__init__()
        self.port = port
        self.data = {
            'bmsql_config': [],
            'bmsql_customer': [],
            'bmsql_district': [],
            'bmsql_history': [], 
            'bmsql_item': [],
            'bmsql_new_order': [],
            'bmsql_oorder': [],
            'bmsql_order_line': [],
            'bmsql_stock': [],
            'bmsql_warehouse': []
        }

    @timer_counter
    def record(self):
        def _convert_to_mb(size):
            units = {
                'KB': 1 / 1024,
                'MB': 1,
                'GB': 1024,
                'TB': 1024 * 1024
            }
            pattern = r'(\d+)\s*(\w+)'
            match = re.match(pattern, size)
            if match:
                value, unit = match.groups()
                if unit in units:
                    return int(value) * units[unit]
            return 0
            
        
        cmd = """gsql -p {0} -d {1} -c '\d+' """.format(self.port, self.dbname)
        p = subprocess.Popen(cmd, stdout=subprocess.PIPE, shell=True)
        output = p.stdout.read().decode("utf-8")
        
        for line in output.split('\n'):
            if not line.startswith(' public'):
                continue
            part = line.split('|')
            tbname = part[1].strip()
            size = part[4].strip()
            
            self.data[tbname].append(_convert_to_mb(size))
        
    def analyze(self):
        return 'table-size', self.add_title(self.data)
            


if __name__ == '__main__':

    duration = int(sys.argv[1])
    interval = int(sys.argv[2])
    dnpath = sys.argv[3]
    print('run: duration={0}s, interval={1}s, dnpath={2}'.format(duration, interval, dnpath))
    

    count = int(duration / interval)
    assert count > 1, 'gan sha ne...'
    
    OG.detect(dnpath)
    Exceler.setExcel('ognmon.xlsx')
    
    thread = ThreadMoniter(OG.procid)
    xlog = XlogNumMoniter(OG.xlogpath)
    tbsize = TableSizeMoniter(OG.port, 'tpcc1000')
    
    for i in range(0, count):
        thread.record()
        xlog.record()
        tbsize.record()
        
        print('\r{0}/{1}'.format(i + 1, count), end = '')
        time.sleep(interval)
    
    
    print('\ndump ... ', end='')
    
    Exceler.recordExcel(thread.analyze_all())
    Exceler.recordExcel(thread.analyze_statistic())
    Exceler.recordExcel(xlog.analyze())
    Exceler.recordExcel(tbsize.analyze())
    
    Exceler.dumpExcel()
    print('ok')

